package com.ycdx.impms.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.ycdx.impms.pojo.Meter;
import com.ycdx.impms.utils.DBPool;

public class MeterImpl {

	static Logger logger = Logger.getLogger(MeterImpl.class.getName());

	/**
	 * 分页查询工具仪表信息
	 * 
	 * @param start
	 * @param number
	 * @param area
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static ArrayList<Meter> getMeterList(int start, int number,
			String area) {
		ArrayList<Meter> result = new ArrayList<Meter>();
		String sql = "";
		if (area.equals("全区")) {
			// 全区权限
			sql = "select * from tb_meter order by diqu,id limit ?,? ";
		} else {
			sql = "select * from tb_meter where diqu = '" + area
					+ "' order by diqu,id limit ?,?";
		}
		// System.out.println(sql);
		Object[] params = { start, number };
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();

		try {
			result = (ArrayList<Meter>) qr.query(conn, sql,
					new BeanListHandler(Meter.class), params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("MeterImpl.class-getMeterList()-数据库操作失败！");
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("MeterImpl.class-getMeterList()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 查询记录总数
	 * 
	 * @param area
	 * @return
	 */
	public static int getCount(String area) {
		Map<String, Object> totalMap = new HashMap<String, Object>();
		int total;
		String sql = "";
		if (area.equals("全区")) {
			// 全区权限
			sql = "select count(*) total from tb_meter;";
		} else {
			sql = "select count(*) total from tb_meter where diqu = '" + area
					+ "';";
		}
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		try {
			totalMap = (HashMap<String, Object>) qr.query(conn, sql,
					new MapHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("MeterImpl.class-getCount()-数据库操作失败！");
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("MeterImpl.class-getCount()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		// total = Integer.parseInt((String)totalMap.get("total"));
		total = Integer.parseInt(totalMap.get("total").toString());
		// System.out.println(total);
		return total;
	}
	
	/**
	 * 添加工具仪表信息
	 * @param meter
	 * @return
	 */
	public static int addMeter(Meter meter){
		//System.out.println(meter.getNote());
		String sql = "insert into tb_meter (" +
				"diqu," 		+ "name,"	+ "quantity,"	+ "factory," +
				"model," 		+ "price,"	+ "buy_date," 	+
				"buy_category," + "note"	+
				") values(?,?,?,?,?,?,?,?,?);";
		Object[] params = {meter.getDiqu(),meter.getName(),meter.getQuantity(),meter.getFactory()
				,meter.getModel(),meter.getPrice(),meter.getBuy_date()
				,meter.getBuy_category(),meter.getNote()};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("MeterImpl.class-addMeter()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("MeterImpl.class-addMeter()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	/**
	 * 更新指定id的工具仪表信息
	 * @param id
	 * @param meter
	 * @return
	 */
	public static int updateMeter(int id,Meter meter){
		String sql = "update tb_meter set " +
				"diqu=?," 			+ "name=?," 		+ "quantity=?," 		+ "factory=?," +
				"model=?," 			+ "price=?," 		+ "buy_date=?," 		+
				"buy_category=?," 	+ "note=?"			+
				"where id=?;";
		Object[] params = {meter.getDiqu(),meter.getName(),meter.getQuantity(),meter.getFactory()
				,meter.getModel(),meter.getPrice(),meter.getBuy_date()
				,meter.getBuy_category(),meter.getNote(),id};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("MeterImpl.class-updateMeter()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("MeterImpl.class-updateMeter()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 根据id删除工具仪表信息
	 * @param id
	 * @return
	 */
	public static int removeMeter(int id){
		String sql = "delete from tb_meter where id=?";
		Object[] params = {id};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("MeterImpl.class-removeMeter()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("MeterImpl.class-removeMeter()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 根据地区查询出工具仪表信息用于导出excel
	 * @param area
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Meter> queryByArea(String area) {
		ArrayList<Meter> result = new ArrayList<Meter>();
		String sql = "";
		if(area.equals("全区")){
			//全区权限
			sql = "select * from tb_meter;";
		}else{
			sql = "select * from tb_meter where diqu = '"+area+"';";
		}
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		try {
			result = (ArrayList<Meter>) qr.query(conn, sql, new BeanListHandler(Meter.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("MeterImpl.class-queryByArea()-数据库操作失败！");
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("MeterImpl.class-queryByArea()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 根据list导出excel
	 * @param allMeter
	 * @return
	 */
	public static InputStream getExcelStream(List<Meter> allMeter) {
	  HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作间
	  HSSFSheet sheet = wb.createSheet("sheet1");//创建一个sheet
	  HSSFRow row = sheet.createRow(0);//创建一行
	  //设置列宽
	  
	  sheet.setColumnWidth(0, 1500);
	  sheet.setColumnWidth(1, 2000);
	  sheet.setColumnWidth(2, 5000);
	  sheet.setColumnWidth(3, 2000);
	  sheet.setColumnWidth(4, 5000);
	  sheet.setColumnWidth(5, 5000);
	  sheet.setColumnWidth(6, 3000);
	  sheet.setColumnWidth(7, 4000);
	  sheet.setColumnWidth(8, 3000);
	  sheet.setColumnWidth(9, 5000);
	  
	  
	  // 设置字体 
      HSSFFont font = wb.createFont();
      font.setFontHeightInPoints(( short ) 10 ); // 字体高度 
      font.setColor(HSSFFont.COLOR_NORMAL); // 字体颜色 
      font.setFontName( "宋体 " ); // 字体 
      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 宽度 
	  
	  //生成一个样式
      HSSFCellStyle style = wb.createCellStyle();
      
      
      //设置这些样式      
      
      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      style.setBorderRight(HSSFCellStyle.BORDER_THIN);
      style.setBorderTop(HSSFCellStyle.BORDER_THIN);
      style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
      
      //新建标题样式
      HSSFCellStyle titlestyle = wb.createCellStyle();
      titlestyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
      titlestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
      titlestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      titlestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      titlestyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
      titlestyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
      titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      titlestyle.setFont(font);
      
      HSSFCell cell = row.createCell(0);//创建这行的第一个元素.从0开始
	  cell.setCellValue(new HSSFRichTextString("编号"));
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(1);//同上,不多解释了
	  cell.setCellValue(new HSSFRichTextString("区域"));
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(2);
	  cell.setCellValue(new HSSFRichTextString("工具仪表名称"));
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(3);
	  cell.setCellValue(new HSSFRichTextString("数量"));
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(4);
	  cell.setCellValue(new HSSFRichTextString("厂家名称"));
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(5);
	  cell.setCellValue(new HSSFRichTextString("型号"));
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(6);
	  cell.setCellValue(new HSSFRichTextString("单价"));
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(7);
	  cell.setCellValue(new HSSFRichTextString("购置日期"));
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(8);
	  cell.setCellValue(new HSSFRichTextString("购买类别"));
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(9);
	  cell.setCellValue(new HSSFRichTextString("备注"));
	  cell.setCellStyle(titlestyle);
	  
	  
	  //循环,将下面几行的数据取出来放入这个sheet中
	  for(int i = 0 ; i < allMeter.size() ; i++)
	  {
	   Meter meter = allMeter.get(i);
	   row = sheet.createRow(i+1);
	   
	   cell = row.createCell(0);
	   cell.setCellValue(meter.getId());
	   cell.setCellStyle(style);
	   
	   cell = row.createCell(1);
	   cell.setCellValue(new HSSFRichTextString(meter.getDiqu()));
	   cell.setCellStyle(style);
	   
	   cell = row.createCell(2);
	   cell.setCellValue(new HSSFRichTextString(meter.getName()));
	   cell.setCellStyle(style);
	   
	   cell = row.createCell(3);
	  cell.setCellValue(new HSSFRichTextString(String.valueOf(meter.getQuantity())));
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(4);
	  cell.setCellValue(new HSSFRichTextString(meter.getFactory()));
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(5);
	  cell.setCellValue(new HSSFRichTextString(meter.getModel()));
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(6);
	  cell.setCellValue(new HSSFRichTextString(String.valueOf(meter.getPrice())));
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(7);
	  cell.setCellValue(new HSSFRichTextString(meter.getBuy_date()));
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(8);
	  cell.setCellValue(new HSSFRichTextString(meter.getBuy_category()));
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(9);
	  cell.setCellValue(new HSSFRichTextString(meter.getNote()));
	  cell.setCellStyle(style);	  
	   
	  }
//	  for(int i=0;i<=20;i++){
//		  sheet.autoSizeColumn(i);
//	  }
	  ByteArrayOutputStream os=new ByteArrayOutputStream();  
      try {  
          wb.write(os);  
            
      } catch (IOException e) {  
          e.printStackTrace();  
      }  
      byte[] bytes=os.toByteArray();  
      InputStream is=new ByteArrayInputStream(bytes);  
      return is;  
	 }
}
